<?php
namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use DB;

class RenewalController extends Controller
{
    public function renewalAppicationList(Request $request) {

        $request_data = $request->input();
        $user_keywords = '';
        $community_id = 0;
        $building_id = 0;
        $units = '';
        $room_no_id = 0;
        $application_start_time = '';
        $application_end_time = '';
        $approval_status = 0;
        if (isset($request_data['user_keywords'])) {
                $user_keywords = $request_data['user_keywords'];
        }
        if (isset($request_data['community_id'])) {
            $community_id = $request_data['community_id'];
        }
        if (isset($request_data['building_id'])) {
            $building_id = $request_data['building_id'];
        }
        if (isset($request_data['units'])) {
            $units = $request_data['units'];
        }
        if (isset($request_data['room_no_id'])) {
            $room_no_id = $request_data['room_no_id'];
        }
        if (isset($request_data['distribution_start_time'])) {
            $application_start_time = $request_data['distribution_start_time'];
        }
        if (isset($request_data['distribution_end_time'])) {
            $application_end_time = $request_data['distribution_end_time'];
        }
        if (isset($request_data['approval_status'])) {
            $approval_status = $request_data['approval_status'];
        }
        $page_size = 10;
        if (isset($request_data['page_size'])) {
            $page_size = $request_data['page_size'];
        }

        $sql = "SELECT rr.renewal_application_id, ra.real_name, ra.phone, ra.idcard, ra.unit_name, rn.community_id, rn.building_id, rn.units, rn.floors, rn.room_no, rr.renewal_end_time, rr.approval_reason, rr.modify_time, rr.approval_uid, rr.relevant_information FROM rcjh_apartment_application ra, rcjh_renewal_appication rr, rcjh_room_no rn  WHERE ra.apartment_application_id = rn.apartment_application_id AND rn.room_no_id = rr.room_no_id AND rr.approval_status = $approval_status";
        if ($user_keywords) {
            $sql .= " AND (ra.real_name LIKE '%$user_keywords%' OR ra.phone LIKE '%$user_keywords%')";
        }
        if ($community_id) {
            $sql .= " AND rn.community_id = $community_id";
        }
        if ($building_id) {
            $sql .= " AND rn.building_id = $building_id";
        }
        if ($units) {
            $sql .= " AND rn.units = '$units'";
        }
        if ($room_no_id) {
            $sql .= " AND rn.room_no_id = $room_no_id";
        }
        if ($application_start_time) {
            $sql .= " AND rr.add_time > '$application_start_time'";
        }
        if ($application_end_time) {
            $sql .= " AND rr.add_time < '$application_end_time'";
        }
        $sql .= " ORDER BY rr.modify_time DESC";

        $response_data = DB::table(DB::raw("($sql limit 9999999) as renewal_appication"))->Paginate($page_size);
        $response_data = $response_data ? $response_data : array();
        foreach ($response_data as $key => $value) {
            $community_name = DB::table('community')->where('community_id', $value->community_id)->value('community_name');
            $building_info = DB::table('building')->where('building_id', $value->building_id)->first(['buildings', 'unit']);
            $approval_name = DB::table('admins')->where('id', $value->approval_uid)->value('name');
            $response_data[$key]->distribution_name = $approval_name ? $approval_name : '';
            $response_data[$key]->community_name = $community_name ? $community_name : '';
            $response_data[$key]->building = $building_info ? $building_info->buildings.$building_info->unit : '';
        }
        return response()->json(['code' => 1, 'data' => $response_data]);
    }

    public function renewalApproval(Request $request) {
        $request_data = $request->input();
        if (!isset($request_data['renewal_application_id']) || !$request_data['renewal_application_id']) {
            return response()->json(['code' => 0, 'message' => '续租申请ID为必填项']);
        }
        if (!isset($request_data['approval_status']) || !$request_data['approval_status']) {
            return response()->json(['code' => 0, 'message' => '审核结果为必填项']);
        }
        if ($request_data['approval_status'] != 1 && (!isset($request_data['approval_reason']) || !$request_data['approval_reason'])) {
            return response()->json(['code' => 0, 'message' => '备注为必填项']);
        }

        DB::beginTransaction();
        try {
            $renewal_application_id = trim($request_data['renewal_application_id']);
            $renewal_appication_data = array(
                'approval_reason' => $request_data['approval_reason'],
                'approval_uid' => $request_data['user']['uid'],
                'approval_status' => $request_data['approval_status']
            );
            $renewal_application_info = DB::table('renewal_appication')->where('renewal_application_id', $renewal_application_id)->first();
            if (!$renewal_application_info) {
                return response()->json(['code' => 0, 'message' => '获取续租申请信息失败']);
            }
            $room_no_info = DB::table('room_no')->where('room_no_id', $renewal_application_info->room_no_id)->first();
            if (!$room_no_info) {
                return response()->json(['code' => 0, 'message' => '获取房源信息失败']);
            }
            $apartment_application = DB::table('apartment_application')->where('apartment_application_id', $room_no_info->apartment_application_id)->first();
            if ($apartment_application) {
                (new MessageController())->addMessage($apartment_application->user_id, 65, "您的续租已处理，请查看");
            }
            if ($request_data['approval_status'] == 1) {
                $room_no_data = array(
                    'distribution_uid' => $request_data['user']['uid'],
                    'lease_period_end_time' => $renewal_application_info->renewal_end_time
                );
                $house_operate_records_data = array(
                    'room_no_id' => $renewal_application_info->room_no_id,
                    'operate_status' => 2,
                    'apartment_application_id' => $room_no_info->apartment_application_id,
                    'operate_uid' => $request_data['user']['uid'],
                    'end_time' => $renewal_application_info->renewal_end_time,
                    'remark' => $request_data['approval_reason']
                );
                if (DB::table('room_no')->where('room_no_id', $renewal_application_info->room_no_id)->update($room_no_data) && DB::table('house_operate_records')->insert($house_operate_records_data) && DB::table('renewal_appication')->where('renewal_application_id', $renewal_application_id)->update($renewal_appication_data)) {
                    DB::commit();
                    return response()->json(['code' => 1, 'message' => '操作成功']);
                } else {
                    DB::rollBack();
                    return response()->json(['code' => 0, 'message' => '操作失败']);
                }
            } else {
                if (DB::table('renewal_appication')->where('renewal_application_id', $renewal_application_id)->update($renewal_appication_data)) {
                    DB::commit();
                    return response()->json(['code' => 1, 'message' => '操作成功']);
                } else {
                    DB::rollBack();
                    return response()->json(['code' => 0, 'message' => '操作失败']);
                }
            }
        }  catch (Exception $e) {
            DB::rollBack();
            return response()->json(['code' => 0, 'message' => '操作失败']);
        }
    }

    public function withdrawalAppicationList(Request $request) {
        $request_data = $request->input();
        $user_keywords = '';
        $community_id = 0;
        $building_id = 0;
        $units = '';
        $room_no_id = 0;
        $approval_start_time = '';
        $approval_end_time = '';
        $approval_status = 0;
        if (isset($request_data['user_keywords'])) {
            $user_keywords = $request_data['user_keywords'];
        }
        if (isset($request_data['community_id'])) {
            $community_id = $request_data['community_id'];
        }
        if (isset($request_data['building_id'])) {
            $building_id = $request_data['building_id'];
        }
        if (isset($request_data['units'])) {
            $units = $request_data['units'];
        }
        if (isset($request_data['room_no_id'])) {
            $room_no_id = $request_data['room_no_id'];
        }
        if (isset($request_data['distribution_start_time'])) {
            $approval_start_time = $request_data['distribution_start_time'];
        }
        if (isset($request_data['distribution_end_time'])) {
            $approval_end_time = $request_data['distribution_end_time'];
        }
        if (isset($request_data['approval_status'])) {
            $approval_status = $request_data['approval_status'];
        }
        $page_size = 10;
        if (isset($request_data['page_size'])) {
            $page_size = $request_data['page_size'];
        }

        $sql = "SELECT rw.withdrawal_appication_id, ra.real_name, ra.phone, ra.idcard, ra.unit_name, rn.community_id, rn.building_id, rn.units, rn.floors, rn.room_no, rw.withdrawal_time, rw.approval_reason, rw.modify_time, rw.approval_uid FROM rcjh_apartment_application ra, rcjh_withdrawal_appication rw, rcjh_room_no rn  WHERE ra.apartment_application_id = rw.apartment_application_id AND rn.room_no_id = rw.room_no_id AND rw.approval_status = $approval_status";
        if ($user_keywords) {
            $sql .= " AND (ra.real_name LIKE '%$user_keywords%' OR ra.phone LIKE '%$user_keywords%')";
        }
        if ($community_id) {
            $sql .= " AND rn.community_id = $community_id";
        }
        if ($building_id) {
            $sql .= " AND rn.building_id = $building_id";
        }
        if ($units) {
            $sql .= " AND rn.units = '$units'";
        }
        if ($room_no_id) {
            $sql .= " AND rn.room_no_id = $room_no_id";
        }
        if ($approval_start_time) {
            $sql .= " AND rw.modify_time > '$approval_start_time'";
        }
        if ($approval_end_time) {
            $sql .= " AND rw.modify_time < '$approval_end_time'";
        }
        $sql .= " ORDER BY rw.modify_time DESC";
        
        $response_data = DB::table(DB::raw("($sql limit 9999999) as withdrawal_appication"))->Paginate($page_size);
        $response_data = $response_data ? $response_data : array();
        foreach ($response_data as $key => $value) {
            $community_name = DB::table('community')->where('community_id', $value->community_id)->value('community_name');
            $building_info = DB::table('building')->where('building_id', $value->building_id)->first(['buildings', 'unit']);
            $approval_name = DB::table('admins')->where('id', $value->approval_uid)->value('name');
            $response_data[$key]->approval_name = $approval_name ? $approval_name : '';
            $response_data[$key]->community_name = $community_name ? $community_name : '';
            $response_data[$key]->building = $building_info ? $building_info->buildings.$building_info->unit : '';
        }
        return response()->json(['code' => 1, 'data' => $response_data]);
    }

    public function withdrawalApproval(Request $request) {
        $request_data = $request->input();
        if (!isset($request_data['withdrawal_appication_id']) || !$request_data['withdrawal_appication_id']) {
            return response()->json(['code' => 0, 'message' => '退租申请ID为必填项']);
        }
        if (!isset($request_data['approval_status']) || !$request_data['approval_status']) {
            return response()->json(['code' => 0, 'message' => '审核结果为必填项']);
        }
        if ($request_data['approval_status'] != 1 && (!isset($request_data['approval_reason']) || !$request_data['approval_reason'])) {
            return response()->json(['code' => 0, 'message' => '备注为必填项']);
        }

        DB::beginTransaction();
        try {
            $withdrawal_appication_id = trim($request_data['withdrawal_appication_id']);
            $withdrawal_appication_info = DB::table('withdrawal_appication')->where('withdrawal_appication_id', $withdrawal_appication_id)->first();
            if (!$withdrawal_appication_info) {
                return response()->json(['code' => 0, 'message' => '获取退租申请信息失败']);
            }
            $room_no_info = DB::table('room_no')->where('room_no_id', $withdrawal_appication_info->room_no_id)->first();
            if (!$room_no_info) {
                return response()->json(['code' => 0, 'message' => '获取房源信息失败']);
            }

            $withdrawal_appication_data = array(
                'approval_reason' => $request_data['approval_reason'],
                'approval_uid' => $request_data['user']['uid'],
                'approval_status' => $request_data['approval_status']
            );
            $apartment_application = DB::table('apartment_application')->where('apartment_application_id', $room_no_info->apartment_application_id)->first();
            if ($apartment_application) {
                (new MessageController())->addMessage($apartment_application->user_id, 66, "您的退租已处理，请查看");
            }
            if ($request_data['approval_status'] == 1) {
                $room_no_data = array(
                    'room_status' => 0,
                    'distribution_uid' => $request_data['user']['uid'],
                    'apartment_application_id' => 0,
                    'lease_period_start_time' => null,
                    'lease_period_end_time' => null
                );
                $house_operate_records_data = array(
                    'room_no_id' => $withdrawal_appication_info->room_no_id,
                    'operate_status' => 4,
                    'apartment_application_id' => $room_no_info->apartment_application_id,
                    'operate_uid' => $request_data['user']['uid'],
                    'end_time' => $withdrawal_appication_info->withdrawal_time,
                    'remark' => $request_data['approval_reason']
                );
                if (DB::table('room_no')->where('room_no_id', $withdrawal_appication_info->room_no_id)->update($room_no_data) && DB::table('house_operate_records')->insert($house_operate_records_data) && DB::table('withdrawal_appication')->where('withdrawal_appication_id', $withdrawal_appication_id)->update($withdrawal_appication_data)) {
                    DB::commit();
                    return response()->json(['code' => 1, 'message' => '操作成功']);
                } else {
                    DB::rollBack();
                    return response()->json(['code' => 0, 'message' => '操作失败']);
                }
            } else {
                if (DB::table('withdrawal_appication')->where('withdrawal_appication_id', $withdrawal_appication_id)->update($withdrawal_appication_data)) {
                    DB::commit();
                    return response()->json(['code' => 1, 'message' => '操作成功']);
                } else {
                    DB::rollBack();
                    return response()->json(['code' => 0, 'message' => '操作失败']);
                }
            }
        }  catch (Exception $e) {
            DB::rollBack();
            return response()->json(['code' => 0, 'message' => '操作失败']);
        }
    }

    public function situationReflectionList(Request $request) {
        $request_data = $request->input();
        $user_keywords = '';
        $community_id = 0;
        $building_id = 0;
        $units = '';
        $room_no_id = 0;
        $handle_start_time = '';
        $handle_end_time = '';
        $handle_status = 0;
        if (isset($request_data['user_keywords'])) {
            $user_keywords = $request_data['user_keywords'];
        }
        if (isset($request_data['community_id'])) {
            $community_id = $request_data['community_id'];
        }
        if (isset($request_data['building_id'])) {
            $building_id = $request_data['building_id'];
        }
        if (isset($request_data['units'])) {
            $units = $request_data['units'];
        }
        if (isset($request_data['room_no_id'])) {
            $room_no_id = $request_data['room_no_id'];
        }
        if (isset($request_data['handle_start_time'])) {
            $handle_start_time = $request_data['handle_start_time'];
        }
        if (isset($request_data['handle_end_time'])) {
            $handle_end_time = $request_data['handle_end_time'];
        }
        if (isset($request_data['handle_status'])) {
            $handle_status = $request_data['handle_status'];
        }
        $page_size = 10;
        if (isset($request_data['page_size'])) {
            $page_size = $request_data['page_size'];
        }

        $sql = "SELECT rs.situatio_reflection_id, ra.real_name, ra.phone, rn.community_id, rn.building_id, rn.units, rn.floors, rn.room_no, rs.description, rs.relevant_information, rs.remark, rs.modify_time FROM rcjh_apartment_application ra, rcjh_situation_reflection rs, rcjh_room_no rn  WHERE ra.apartment_application_id = rs.apartment_application_id AND rn.room_no_id = rs.room_no_id AND rs.handle_status = $handle_status";
        if ($user_keywords) {
            $sql .= " AND (ra.real_name LIKE '%$user_keywords%' OR ra.phone LIKE '%$user_keywords%')";
        }
        if ($community_id) {
            $sql .= " AND rn.community_id = $community_id";
        }
        if ($building_id) {
            $sql .= " AND rn.building_id = $building_id";
        }
        if ($units) {
            $sql .= " AND rn.units = '$units'";
        }
        if ($room_no_id) {
            $sql .= " AND rn.room_no_id = $room_no_id";
        }
        if ($handle_start_time) {
            $sql .= " AND rs.modify_time > '$handle_start_time'";
        }
        if ($handle_end_time) {
            $sql .= " AND rs.modify_time < '$handle_end_time'";
        }
        $sql .= " ORDER BY rs.modify_time DESC";

        $response_data = DB::table(DB::raw("($sql limit 9999999) as withdrawal_appication"))->Paginate($page_size);
        $response_data = $response_data ? $response_data : array();
        foreach ($response_data as $key => $value) {
            $community_name = DB::table('community')->where('community_id', $value->community_id)->value('community_name');
            $building_info = DB::table('building')->where('building_id', $value->building_id)->first(['buildings', 'unit']);
            $response_data[$key]->community_name = $community_name ? $community_name : '';
            $response_data[$key]->building = $building_info ? $building_info->buildings.$building_info->unit : '';
        }
        return response()->json(['code' => 1, 'data' => $response_data]);
    }

    public function situationReflectionHandle(Request $request) {
        $request_data = $request->input();
        if (!isset($request_data['situatio_reflection_id']) || !$request_data['situatio_reflection_id']) {
            return response()->json(['code' => 0, 'message' => '情况反应ID为必填项']);
        }
        if (!isset($request_data['remark']) || !$request_data['remark']) {
            return response()->json(['code' => 0, 'message' => '备注为必填项']);
        }

        $situation_reflection = DB::table('situation_reflection')->where('situatio_reflection_id', $request_data['situatio_reflection_id'])->first();
        if($situation_reflection) {
            $room_no_info = DB::table('room_no')->where('room_no_id', $situation_reflection->room_no_id)->first();
            if ($room_no_info) {
                $apartment_application = DB::table('apartment_application')->where('apartment_application_id', $room_no_info->apartment_application_id)->first();
                if ($apartment_application) {
                    (new MessageController())->addMessage($apartment_application->user_id, 67, "您的情况反应已处理，请查看");
                }
            }
        }
        if (DB::table('situation_reflection')->where('situatio_reflection_id', $request_data['situatio_reflection_id'])->update(['handle_status' => 1, 'remark' => $request_data['remark'], 'handle_uid' => $request_data['user']['uid']])) {
            return response()->json(['code' => 1, 'message' => '操作成功']);
        } else {
            return response()->json(['code' => 0, 'message' => '操作失败']);
        }
    }

    public function houseRecordsList(Request $request) {
        $request_data = $request->input();
        $user_keywords = '';
        $community_id = 0;
        $building_id = 0;
        $units = '';
        $room_no_id = 0;
        $start_time = '';
        $end_time = '';
        $operate_status = '';
        if (isset($request_data['user_keywords'])) {
            $user_keywords = $request_data['user_keywords'];
        }
        if (isset($request_data['community_id'])) {
            $community_id = $request_data['community_id'];
        }
        if (isset($request_data['building_id'])) {
            $building_id = $request_data['building_id'];
        }
        if (isset($request_data['units'])) {
            $units = $request_data['units'];
        }
        if (isset($request_data['room_no_id'])) {
            $room_no_id = $request_data['room_no_id'];
        }
        if (isset($request_data['start_time'])) {
            $start_time = $request_data['start_time'];
        }
        if (isset($request_data['end_time'])) {
            $end_time = $request_data['end_time'];
        }
        if (isset($request_data['operate_status'])) {
            $operate_status = $request_data['operate_status'];
        }
        $page_size = 10;
        if (isset($request_data['page_size'])) {
            $page_size = $request_data['page_size'];
        }

        $sql = "SELECT rr.house_operate_record_id, ra.real_name, ra.phone, ra.idcard, ra.unit_name, rn.community_id, rn.building_id, rn.units, rn.floors, rn.room_no, rr.operate_uid, rr.operate_status, rr.add_time FROM rcjh_apartment_application ra, rcjh_house_operate_records rr, rcjh_room_no rn  WHERE ra.apartment_application_id = rn.apartment_application_id AND rn.room_no_id = rr.room_no_id";
        if ($user_keywords) {
            $sql .= " AND (ra.real_name LIKE '%$user_keywords%' OR ra.phone LIKE '%$user_keywords%')";
        }
        if ($community_id) {
            $sql .= " AND rn.community_id = $community_id";
        }
        if ($building_id) {
            $sql .= " AND rn.building_id = $building_id";
        }
        if ($units) {
            $sql .= " AND rn.units = '$units'";
        }
        if ($room_no_id) {
            $sql .= " AND rn.room_no_id = $room_no_id";
        }
        if ($start_time) {
            $sql .= " AND rr.add_time > '$start_time'";
        }
        if ($end_time) {
            $sql .= " AND rr.add_time < '$end_time'";
        }
        if (strlen($operate_status)) {
            $sql .= " AND rr.operate_status = $operate_status";
        }
        $sql .= " ORDER BY rr.add_time DESC";

        $response_data = DB::table(DB::raw("($sql limit 9999999) as withdrawal_appication"))->Paginate($page_size);
        $response_data = $response_data ? $response_data : array();
        foreach ($response_data as $key => $value) {
            $community_name = DB::table('community')->where('community_id', $value->community_id)->value('community_name');
            $building_info = DB::table('building')->where('building_id', $value->building_id)->first(['buildings', 'unit']);
            $approval_name = DB::table('admins')->where('id', $value->operate_uid)->value('name');
            $response_data[$key]->approval_name = $approval_name ? $approval_name : '';
            $response_data[$key]->community_name = $community_name ? $community_name : '';
            $response_data[$key]->building = $building_info ? $building_info->buildings.$building_info->unit : '';
        }
        return response()->json(['code' => 1, 'data' => $response_data]);
    }
}